﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;

namespace DbLayer
{
    class SqlUtility
    {
        public SqlUtility()
        {

        }
        //Get Connection
        public SqlConnection GetCon()
        {
            //SqlConnection connect = new SqlConnection("Data Source=LARS-ASUS;Initial Catalog=Dmaa0912_E6; Persist Security Info=True;User ID=test01;Password=test01");
            SqlConnection connect = new SqlConnection("Data Source=balder.ucn.dk;Initial Catalog=dmaa0912_E6;Persist Security Info=True;User ID=dmaa0912_E6;Password=MaaGodt");
            return connect;
        }
        //Execute date returns a int +1 for succes and -1 for failure
        public int ExecuteData(SqlCommand _cmd, SqlConnection _con)
        {
            int result = -1;
            SqlTransaction transaction = null;
                transaction = _con.BeginTransaction();
                _cmd.Transaction = transaction;
            try
            {
                int i = _cmd.ExecuteNonQuery();
                if (i > 0)
                {
                    result = 1;
                }
                else
                {
                    result = -1;
                }
                transaction.Commit();
            }
            catch (SqlException ex)
            {
                transaction.Rollback();
                result = -1;
                System.Console.WriteLine("Fejl: " + ex.Message);
            }
            _con.Close();
            _con.Dispose();
            return result;
        }
        //Execute date returns a int +1 for succes and -1 for failure
        public int ExecuteMultipleData(SqlCommand _cmd, SqlCommand _cmd2, SqlConnection _con)
        {
            int result = -1;
            SqlTransaction transaction = null;
            transaction = _con.BeginTransaction();
                _cmd.Transaction = transaction;
                _cmd2.Transaction = transaction;

            try
            {
                int i = _cmd.ExecuteNonQuery();
                int j = _cmd2.ExecuteNonQuery();
                if (i > 0 && j > 0)
                {
                    result = 1;
                }
                else
                {
                    result = -1;
                }
                transaction.Commit();
            }
            catch (SqlException ex)
            {
                result = -1;
                transaction.Rollback();
                System.Console.WriteLine("Fejl: " + ex.Message);
            }
            _con.Close();
            _con.Dispose();
            return result;
        }

        public string GetSafeString(SqlDataReader _rdr, int index)
        {
            if(!_rdr.IsDBNull(index))
            {
                return _rdr.GetString(index);      
            }
            else
            {
                return string.Empty;
            }
        }
        public int GetSafeInt(SqlDataReader _rdr, int index)
        {
            if (!_rdr.IsDBNull(index))
            {
                return _rdr.GetInt32(index);
            }
            else
            {
                return 0;
            }
        }

        public Boolean getSafeBool(SqlDataReader _rdr, int index)
        {
            if (!_rdr.IsDBNull(index))
            {
                return _rdr.GetBoolean(index);
            }
            else
            {
                return false;
            }

        }

        public double getSafeDouble(SqlDataReader _rdr, int index)
        {
            if (!_rdr.IsDBNull(index))
            {
                decimal dp =_rdr.GetDecimal(index);

                double ret = (double)dp;
                return ret;

               
            }
            else
            {
                return 0;
            }

        }

    }
}
